import os
import pandas as pd
file_path=os.getcwd()+"\\门店\\"
result_path=os.getcwd()+"\\结果\\"
file_store=os.listdir(file_path)
DataFrame=pd.read_excel(file_path+file_store[0],header=0)
list_Need=["名称","经纪人人数","二手房新增","租赁新增","二手客户-新增","租赁客户-新增",\
           "二手房总数-公盘","二手房总数-私盘",\
           "租赁总数-公盘","租赁总数-私盘"]

DataStore=DataFrame[list_Need].copy()
store_txt=open(os.getcwd()+"\\筛选\\"+'删除门店.json',encoding = "utf-8")
listSTORE=list(store_txt)
listSTORE=[x.strip() for x in listSTORE if x.strip()!='']
store_txt.close()

for store in listSTORE:
    DataStore=DataStore[~DataStore["名称"].isin([store])]
    
#DataStore.insert(1,"人员数量"," ", allow_duplicates=False)
DataStore.insert(3,"二手房人均新增"," ", allow_duplicates=False)
DataStore.insert(5,"租赁人均新增"," ", allow_duplicates=False)
DataStore.insert(7,"二手房客户人均新增"," ", allow_duplicates=False)
DataStore.insert(9,"租赁客户人均新增"," ", allow_duplicates=False)
DataStore.insert(11,"二手房人均公盘"," ", allow_duplicates=False)
DataStore.insert(13,"二手房人均私盘"," ", allow_duplicates=False)
DataStore.insert(15,"租赁人均公盘"," ", allow_duplicates=False)
DataStore.insert(17,"租赁人均私盘"," ", allow_duplicates=False)
#计算人均
DataStore['二手房人均新增']=DataStore['二手房新增']/DataStore['经纪人人数']
DataStore['租赁人均新增']=DataStore['租赁新增']/DataStore['经纪人人数']
DataStore['二手房客户人均新增']=DataStore['二手客户-新增']/DataStore['经纪人人数']
DataStore['租赁客户人均新增']=DataStore['租赁客户-新增']/DataStore['经纪人人数']
DataStore['二手房人均公盘']=DataStore['二手房总数-公盘']/DataStore['经纪人人数']
DataStore['二手房人均私盘']=DataStore['二手房总数-私盘']/DataStore['经纪人人数']
DataStore['租赁人均公盘']=DataStore['租赁总数-公盘']/DataStore['经纪人人数']
DataStore['租赁人均私盘']=DataStore['租赁总数-私盘']/DataStore['经纪人人数']
format_2f="{:.02f}".format
Average_List=['二手房人均新增','租赁人均新增','二手房客户人均新增','租赁客户人均新增',\
              '二手房人均公盘','二手房人均私盘','租赁人均公盘','租赁人均私盘']
for i in Average_List:
    DataStore[i]=DataStore[i].apply(format_2f)
    DataStore[i]=DataStore[i].astype(float)
save_path=result_path+"门店数据.xlsx"
DataStore.to_excel(save_path,index=False)

def Area():
    file_path2=os.getcwd()+"\\区域\\"
    file_area=os.listdir(file_path2)
    file_path_screen=os.getcwd()+"\\筛选\\"
    DataArea=pd.DataFrame()
    for i in file_area:
        DataFrame=pd.read_excel(file_path2+i,header=0)
        DataArea=pd.concat([DataArea,DataFrame])
    DataArea=DataArea[list_Need].copy()
    area_txt=open(file_path_screen+'删除区域.json',encoding = "utf-8")
    listAREA=list(area_txt)
    listAREA=[x.strip() for x in listAREA if x.strip()!='']
    area_txt.close()
    for area in listAREA:
        DataArea=DataArea[~DataArea["名称"].isin([area])]
    DataArea.insert(3,"二手房人均新增"," ", allow_duplicates=False)
    DataArea.insert(5,"租赁人均新增"," ", allow_duplicates=False)
    DataArea.insert(7,"二手房客户人均新增"," ", allow_duplicates=False)
    DataArea.insert(9,"租赁客户人均新增"," ", allow_duplicates=False)
    DataArea.insert(11,"二手房人均公盘"," ", allow_duplicates=False)
    DataArea.insert(13,"二手房人均私盘"," ", allow_duplicates=False)
    DataArea.insert(15,"租赁人均公盘"," ", allow_duplicates=False)
    DataArea.insert(17,"租赁人均私盘"," ", allow_duplicates=False)
    #计算人均
    DataArea['二手房人均新增']=DataArea['二手房新增']/DataArea['经纪人人数']
    DataArea['租赁人均新增']=DataArea['租赁新增']/DataArea['经纪人人数']
    DataArea['二手房客户人均新增']=DataArea['二手客户-新增']/DataArea['经纪人人数']
    DataArea['租赁客户人均新增']=DataArea['租赁客户-新增']/DataArea['经纪人人数']
    DataArea['二手房人均公盘']=DataArea['二手房总数-公盘']/DataArea['经纪人人数']
    DataArea['二手房人均私盘']=DataArea['二手房总数-私盘']/DataArea['经纪人人数']
    DataArea['租赁人均公盘']=DataArea['租赁总数-公盘']/DataArea['经纪人人数']
    DataArea['租赁人均私盘']=DataArea['租赁总数-私盘']/DataArea['经纪人人数']
    for p in Average_List:
        DataArea[p]=DataArea[p].apply(format_2f)
        DataArea[p]=DataArea[p].astype(float)
    save_path_area=result_path+"区域数据.xlsx"
    DataArea.to_excel(save_path_area,index=False)
Area()
print("完成")
